<!DOCTYPE html>
<html lang="zh-cmn-Hans" prefix="og: http://ogp.me/ns#" class="han-init">
<head>
  <meta charset="utf-8">
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
  <link rel="icon" href="http://upeng.github.io/favicon.ico">
  <title>upeng</title>
  <link rel="stylesheet" href="/css/style.css">
  <link rel="stylesheet" href="/lib/fancybox/jquery.fancybox-1.3.4.css">
  <!--在这里倒入jquery 方便处理部分页面的jquery-->
  <script src="https://cdn.staticfile.org/jquery/1.7/jquery.min.js" type="text/javascript" ></script>
</head>

<body>
	<header class="site-header navfixed-false">
  <div class="container">
      <h1><a href="/" title="upeng"><span class="octicon octicon-mark-github"></span> upeng</a></h1>
      <nav class="site-header-nav" role="navigation">
        
              
              <a href="/"  class=" site-header-nav-item hvr-underline-from-center" title="Home">Home</a>
        
              
              <a href="/categories/"  class=" site-header-nav-item hvr-underline-from-center" title="Categories">Categories</a>
        
              
              <a href="/bookmark/"  class=" site-header-nav-item hvr-underline-from-center" title="Bookmark">Bookmark</a>
        
              
              <a href="http://shareup.sinaapp.com"  class=" site-header-nav-item hvr-underline-from-center" title="Share">Share</a>
        
              
              <a href="/about/"  class=" site-header-nav-item hvr-underline-from-center" title="About">About</a>
        
      </nav>
  </div>
</header>

	
<section class="collection-head geopattern" data-pattern-id="mysql主从复制原理理解与实践" >
    <div class="container">
        <div class="collection-title">
            <h1 class="collection-header">
                mysql主从复制原理理解与实践
            </h1>
            
                <div class="collection-info">
                    <span class="meta-info">
                        <span class="octicon octicon-calendar"></span>
                        <time datetime="2016-12-05T13:42:47.000Z" itemprop="datePublished">2016-12-05</time>
                    </span>
                    
                        <span class="meta-info">
                            <span class="octicon octicon-file-directory"></span>
                            <a href='/categories/MySQL/' title=''>MySQL</a>
                        </span>
                    
                </div>
            
        </div>
    </div>
</section>
	<section class="container">
    <div class="columns">
        <!-- -->
        <div class="column three-fourths">
            <article class="article-content markdown-body">
                <p>只从最简单的mysql主从复制原理与实践着手，之前看到Yshiba老师讲解的挺细致透彻，顺便做下笔记并实践一下。</p>
<h2 id="主从复制原理-replication"><a href="#主从复制原理-replication" class="headerlink" title="主从复制原理 replication"></a>主从复制原理 replication</h2><p>主服务器(master)的binlog,记录master的各种操作（insert、update、delete），master的磁盘只要发生变化就会记录到自己的binlog里面。</p>
<p>从服务器（slave）如果能读取到master的binlog,那么就可以将master的所做操作跟着执行一遍。但是由于master的binlog中含有master的一些信息，所以需要对binlog进行分析加工，slave的relaylog就是干这种事的，实际上slave将使用relaylog终极日志上的sql操作，保证主从数据一致性。<br><a id="more"></a><br><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/mysql-master-slave.gif" alt="mysql-master-slave"></p>
<p><strong>总结</strong>：</p>
<ol>
<li>master要配置binlog</li>
<li>slave要配置relaylog</li>
<li>master需要grant slave账号使得slave有权读取master的binlog</li>
<li>slave使用账号连接master</li>
</ol>
<h2 id="主从配置过程"><a href="#主从配置过程" class="headerlink" title="主从配置过程"></a>主从配置过程</h2><h3 id="编辑master的配置文件my-cnf"><a href="#编辑master的配置文件my-cnf" class="headerlink" title="编辑master的配置文件my.cnf"></a>编辑master的配置文件my.cnf</h3><pre><code>#1.配置server-id
server-id=101

#2.开启二进制日志
log-bin=mysql-bin

#3.指定二进制日志格式
binlog-format=mixed
</code></pre><blockquote>
<p>二进制日志格式binlog-format</p>
</blockquote>
<ol>
<li>statement：二进制记录的是执行语句，update insert…</li>
<li>row：二进制执行的是磁盘变化</li>
<li>mixed：混合的，由系统根据语句决定</li>
</ol>
<blockquote>
<p>到底使用哪种format，根据实际情况判断</p>
</blockquote>
<p><strong>比如：</strong><br>磁盘变化少，语句长，使用row<br><code>update user set age = age + 1 where id = 2;</code><br>磁盘变化多，语句短，使用statement<br><code>update slary = slary + 1000;</code> (10w条数据)</p>
<h3 id="编辑slave的配置文件my-cnf"><a href="#编辑slave的配置文件my-cnf" class="headerlink" title="编辑slave的配置文件my.cnf"></a>编辑slave的配置文件my.cnf</h3><pre><code>#1.配置server-id
server-id=102

#2.开启二进制日志(备份)
log-bin=mysql-bin

#3.创建relay日志
relay-log=mysql-relay
</code></pre><blockquote>
<p>配置完成后重启两个机器的mysql服务器<br><code>service mysqld restart</code></p>
</blockquote>
<h2 id="启动主从建立关系"><a href="#启动主从建立关系" class="headerlink" title="启动主从建立关系"></a>启动主从建立关系</h2><h3 id="master的操作"><a href="#master的操作" class="headerlink" title="master的操作"></a>master的操作</h3><p>查看主服务器的状态<br><code>show master status</code></p>
<pre><code>#授权账号密码
grant replication clinet,replication slave on *.* to test@&#39;192.168.1.%&#39; identified by &#39;123qwe&#39;;

flush privileges;
</code></pre><h3 id="slave的操作"><a href="#slave的操作" class="headerlink" title="slave的操作"></a>slave的操作</h3><p>建立连接</p>
<pre><code>change master to
master_host=&#39;192.168.1.101&#39;
master_user=&#39;test&#39;
master_password=&#39;123qwe&#39;
master_log_file=&#39;mysql-bin.000001&#39;
master_log_pass=348
</code></pre><p>查看连接状态<br><code>show slave status</code></p>
<p>启动从服务器同步进程并查看状态<br><code>start slave</code></p>
<blockquote>
<p>如果报错Last_IO_Error: error connect to master ‘test@192.168.1.101:3306’ ……<br>使用Telnet 192.168.1.101 3306排查连接master是否成功<br>有可能是防火墙开了，关闭防火墙：<code>services iptables stop</code></p>
</blockquote>
<pre><code>stop slave;
start slave;
show slave status;
</code></pre><h3 id="验证测试"><a href="#验证测试" class="headerlink" title="验证测试"></a>验证测试</h3><p>在mater中创建数据库和表，并做各种增删改操作，到从库观察验证</p>
<h2 id="补充说明"><a href="#补充说明" class="headerlink" title="补充说明"></a>补充说明</h2><p><strong>mysql经常报错如下：</strong><br><code>can&#39;t connect to local mysql server through socket &#39;/tmp/mysqld.sock&#39;</code><br>通过<code>/tmp/mysqld.sock</code>下的socket文件连接不成功，比如我们默认安装的mysql通过配置文件可以发现mysql.sock在<code>/var/lib/mysql/mysql.sock</code>下。也就是说/tmp/下没有mysqld.sock文件</p>
<p><strong>有两种解决方法：</strong></p>
<ol>
<li>指定sock文件位置<br><code>mysql -uroot -p -S /var/lib/mysql/mysql.sock</code></li>
<li>建立一个软连接<br><code>ln -s /var/lib/mysql/mysql.sock /tmp/mysqld.sock</code></li>
</ol>
<h2 id="后续学习计划"><a href="#后续学习计划" class="headerlink" title="后续学习计划"></a>后续学习计划</h2><p>主从复制可能会出现的问题级解决方案<br>mysql读写分离策略</p>

            </article>
            
                <div class="share">
                    <!--开启分享-->
<div class="share-component" data-disabled="google,twitter,facebook" data-description="只从最简单的mysql主从复制原理与实践着手，之前看到..."></div>

<script src="/js/share.min.js"></script>

                </div>    
            
            
                
<div class="comments">
	<div class="ds-thread" data-thread-key="mysql-master-slave" data-title="mysql主从复制原理理解与实践" data-url="http://upeng.github.io/2016/12/05/mysql-master-slave/"></div>
	<script type="text/javascript">
	var duoshuoQuery = {short_name:"upeng"};
		(function() {
			var ds = document.createElement('script');
			ds.type = 'text/javascript';ds.async = true;
			ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
			ds.charset = 'UTF-8';
			(document.getElementsByTagName('head')[0]
			 || document.getElementsByTagName('body')[0]).appendChild(ds);
		})();
	</script>
</div>

            
        </div>
        <div class="column one-fourth">
            
                
                


<h3>Post Directory</h3>

<div id="post-directory-module">
	<section class="post-directory">
		<p><strong class="toc-title">文章目录</strong></p>
		<ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#主从复制原理-replication"><span class="toc-text">主从复制原理 replication</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#主从配置过程"><span class="toc-text">主从配置过程</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#编辑master的配置文件my-cnf"><span class="toc-text">编辑master的配置文件my.cnf</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#编辑slave的配置文件my-cnf"><span class="toc-text">编辑slave的配置文件my.cnf</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#启动主从建立关系"><span class="toc-text">启动主从建立关系</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#master的操作"><span class="toc-text">master的操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#slave的操作"><span class="toc-text">slave的操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#验证测试"><span class="toc-text">验证测试</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#补充说明"><span class="toc-text">补充说明</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#后续学习计划"><span class="toc-text">后续学习计划</span></a></li></ol>
	</section>
</div>
            
        </div>
    </div>
</section>

<footer class="container">
    <div class="site-footer" role="contentinfo">
        <div class="copyright left mobile-block">
                © 2016
                <span title="yupeng">yupeng</span>
                <a href="javascript:window.scrollTo(0,0)" class="right mobile-visible">TOP</a>
        </div>

        <ul class="site-footer-links right mobile-hidden">
            <li>
                <a href="javascript:window.scrollTo(0,0)" >TOP</a>
            </li>
        </ul>

        <a href="https://github.com/upeng" target="_blank" aria-label="view source code">
            <span class="mega-octicon octicon-mark-github" title="GitHub"></span>
        </a>

        <ul class="site-footer-links mobile-hidden">
            
                  
                  <li>
                    <a href="/"  title="Home">Home</a>
                  </li>
            
                  
                  <li>
                    <a href="/categories/"  title="Categories">Categories</a>
                  </li>
            
                  
                  <li>
                    <a href="/bookmark/"  title="Bookmark">Bookmark</a>
                  </li>
            
                  
                  <li>
                    <a href="http://shareup.sinaapp.com"  title="Share">Share</a>
                  </li>
            
                  
                  <li>
                    <a href="/about/"  title="About">About</a>
                  </li>
            
            <li>
                <a href="/atom.xml">
                    <span class="octicon octicon-rss" style="color:orange;"></span>
                </a>
            </li>
        </ul>
    </div>
</footer>


		<script src="/js/geopattern.js"></script>

		
			<script src="/js/toc.js"></script>
		

		<script src="/js/highlight.pack.js"></script>
		<script src="/lib/fancybox/jquery.fancybox-1.3.4.pack.js"></script>

		<script src="/js/index.js"></script>

		 <script src="/js/popular_repo.js"></script> 

	</body>
</html>